/*
 * Copyright (C) 2009 2DAI
 *
 * Este programa es Software Libre; usted puede redistribuirlo
 * y/o modificarlo bajo los términos de la "GNU General Public
 * License" como lo publica la "FSF Free Software Foundation",
 * o (a su elección) de cualquier versión posterior.
 *
 * Este programa es distribuido con la esperanza de que le será
 * útil, pero SIN NINGUNA GARANTIA; incluso sin la garantía
 * implícita por el MERCADEO o EJERCICIO DE ALGUN PROPOSITO en
 * particular. Vea la "GNU General Public License" para más
 * detalles.
 *
 * Usted debe haber recibido una copia de la "GNU General Public
 * License" junto con este programa, si no, escriba a la "FSF
 * Free Software Foundation, Inc.", 59 Temple Place - Suite 330,
 * Boston, MA  02111-1307, USA.
 */
package data;

import com.mysql.jdbc.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import on.Players;
import on.Team;
import util.Errors;

/**
 *
 * Autor: Marta Espejo
 */
public class DataPlayers extends DataManager<Players> {
    ArrayList<Team> equipos;

    private String tabla = "players";
    private String tabla2="team";

    public DataPlayers() {
        super();
        setTabla(tabla);
        requerir();
    }

  @Override
    public void requerir() {
        try {
            //conectamos
            sql.conectar();
            //recojemos los resultados. En la select utilizanos la variable que
            //contiene el nombre de la tabla.
            ResultSet rs = (ResultSet) sql.ejecutarSelect("select id_players,num_license," +
                    "last_name,first_name,birth_date,dni,class,nacionality," +
                    "postal_code,address,city,province,name,team.league,phone," +
                    "players.email from " + tabla+" join " +tabla2+" on(players.num_team=team.id_team)");
            //mientras hayan resultados los vamos insertando en el array de jugadores
            while (rs.next()) {
                Players player = new Players();
                player.setId_jugadores(rs.getInt(1));
                player.setNumLicencia(rs.getInt(2));
                player.setApellidos(rs.getString(3));
                player.setNombre(rs.getString(4));
                player.setFechaNacimiento(rs.getDate(5));
                player.setDni(rs.getInt(6));
                player.setCategoria(rs.getString(7));
                player.setNacionalidad(rs.getString(8));
                player.setCodigoPostal(rs.getInt(9));
                player.setDireccion(rs.getString(10));
                player.setPoblacion(rs.getString(11));
                player.setProvincia(rs.getString(12));
                player.setEquipo(rs.getString(13));
                player.setLiga(rs.getString(14));
                player.setTelefono(rs.getInt(15));
                player.setEMail(rs.getString(16));
                getList().add(player);
            }
        } catch (SQLException ex) {
            Errors.creaError("ERROR: No se han podido conseguir los datos\n" + ex.getMessage(), ex.getStackTrace());
        } finally {
            sql.desconectar();
        }

    }

    @Override
    public void guardar(Players o) {
        getList().set(index, o);
        sql.conectar();
        sql.ejectarUpdate("UPDATE " + tabla + " " +
                "SET num_license= " + o.getNumLicencia() + "," +
                "last_name='" + o.getApellidos() + "'," +
                "first_name='" + o.getNombre() + "'," +
                "birth_date='" + o.getFechaNacimiento() + "'," +
                "dni='" + o.getDni() + "'," +
                "class='" + o.getCategoria() + "'," +
                "nacionality='" + o.getNacionalidad() + "'," +
                "postal_code=" + o.getCodigoPostal() + "," +
                "address='" + o.getDireccion() + "'," +
                "city='" + o.getPoblacion() + "'," +
                "province='" + o.getProvincia() + "'," +
                "num_team=" + o.getNumeroEquipo() + "," +
                "league='" + o.getLiga() + "'," +
                "phone=" + o.getTelefono() + "," +
                "email='" + o.getEMail() + "' " +
                " WHERE id_players=" + o.getId_jugadores());
        sql.desconectar();
    }

    @Override
    public void insertar(Players o) {
        sql.conectar();
        getList().add(o);
        String d = String.format("%1$tY-%1$tm-%1$t" + "e", o.getFechaNacimiento());       
        sql.ejectarUpdate("INSERT INTO " + tabla + " (num_license," +
                    "last_name,first_name,birth_date,dni,class,nacionality," +
                    "postal_code,address,city,province,num_team,league,phone," +
                    "email) " +
                " VALUES ("+ o.getNumLicencia() + "," +
                "'" + o.getApellidos() + "','" + o.getNombre() + "'," +
                "'" + d + "'," + o.getDni() + ",'" + o.getCategoria() + "'," +
                "'" + o.getNacionalidad() + "'," + o.getCodigoPostal() + ",'" + o.getDireccion() + "'," +
                "'" + o.getPoblacion() + "','" + o.getProvincia() + "','" + o.getNumeroEquipo() + "'," +
                "'" + o.getLiga() + "'," + o.getTelefono() + ",'" + o.getEMail() + "')");
        sql.desconectar();
    }

    @Override
    public void eliminar(Players o) {
        sql.conectar();
        getList().remove(index);
        sql.ejectarUpdate("DELETE FROM " + tabla + " " +
                " WHERE id_players= " + o.getId_jugadores());
        sql.desconectar();
    }

    /**
     * mÃ©todo que pasandole el id del jugador nos devuelve toda su informaciÃ³n
     * @param id
     * @return
     */
    public Players obtenerJugador(int id) {
        Players player = new Players();
        try {
            sql.conectar();
            //getList().remove(index);
            ResultSet rs = (ResultSet) sql.ejecutarSelect("SELECT * FROM " + tabla +
                    " WHERE id_players= " + id);
            rs.next();

            player.setNumLicencia(rs.getInt(2));
            player.setApellidos(rs.getString(3));
            player.setNombre(rs.getString(4));
            player.setFechaNacimiento(rs.getDate(5));
            player.setDni(rs.getInt(6));
            player.setCategoria(rs.getString(7));
            player.setNacionalidad(rs.getString(8));
            player.setCodigoPostal(rs.getInt(9));
            player.setDireccion(rs.getString(10));
            player.setPoblacion(rs.getString(11));
            player.setProvincia(rs.getString(12));
            player.setNumeroEquipo(rs.getInt(13));
            player.setLiga(rs.getString(14));
            player.setTelefono(rs.getInt(15));
            player.setEMail(rs.getString(16));

        } catch (SQLException ex) {
            Errors.creaError("ERROR: No se han podido conseguir los datos\n" + ex.getMessage(), ex.getStackTrace());
        } finally {
            sql.desconectar();
            return player;
        }
    }



    @Override
    public void crear(String tabla) {
        sql.conectar();
        sql.ejectarUpdate("DROP TABLE IF EXISTS " + tabla + ";");
        sql.ejectarUpdate("CREATE TABLE IF NOT EXISTS " + tabla + " (" +
                "id_players int(11) NOT NULL auto_increment," +
                "num_license int(11) NOT NULL," +
                "last_name varchar(20) NOT NULL," +
                "first_name varchar(20) NOT NULL," +
                "birth_date date NOT NULL," +
                "dni int(8) NOT NULL," +
                "class varchar(3) NOT NULL," +
                "nacionality varchar(3) NOT NULL," +
                "postal_code int(6) NOT NULL," +
                "address varchar(30) NOT NULL," +
                "city varchar(30) NOT NULL," +
                "province varchar(30) NOT NULL," +
                "num_team int(20) NOT NULL," +
                "league varchar(3) NOT NULL," +
                "phone int(11) default NULL," +
                "email varchar(40) default NULL," +
                "PRIMARY KEY  (id_players) )" +
                "ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;");
        sql.desconectar();
    }

     public ArrayList<Team> obtenerClub(){
      ArrayList<Team> l =new ArrayList<Team>();
     try{
     sql.conectar();
     //getList().remove(index);
       ResultSet rs = (ResultSet) sql.ejecutarSelect("SELECT id_team,name FROM " + tabla2 );
       int i=0;
       while(rs.next()){
              Team equipo = new Team();
                equipo.setId_equipo(rs.getInt("id_team"));
                equipo.setNombre(rs.getString("name"));

         l.add(equipo);
       }
     }catch(SQLException ex){
        Errors.creaError("ERROR: No se han podido conseguir los datos\n" + ex.getMessage(), ex.getStackTrace());
     }finally{
         sql.desconectar();
         return l;
     }

    }
}
